﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using Mapeo;
using NHibernate;

namespace DAL
{
    public class Secretaria_DAL:Controlador
    {

        public DataTable DATA_MATRIC1_ListaNominas(String semestre, String ciclo, String especialidad)
        {
            //Lista todas las especialidades de la tabla data_notas

            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter adpnota3 = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();

            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_DATA_MATRIC1_ListaNominas";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_ciclo", ciclo);
                cmd.Parameters.AddWithValue("@in_Especialidad", especialidad);


                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                adpnota3 = new MySqlDataAdapter(cmd);
                adpnota3.Fill(data);

                return data;


            }
            catch (Exception ex)
            {
                throw ex;

            }

            finally
            {
                conexion.Close();
                cmd.Dispose();

            }
        }

        /*
        public DataTable DATA_OBS_Listar(String inSemestre, String inCodAlumno, String inNombre, String indescripcion, String tipo)
        {
            //Lista todas las Licenciass
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter adpnota3 = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();

            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_DATA_LISTA_DATA_OBS";
                cmd.Parameters.AddWithValue("@inSemestre", inSemestre);
                cmd.Parameters.AddWithValue("@inCodAlumno", inCodAlumno);
                cmd.Parameters.AddWithValue("@inNombre", inNombre);
                cmd.Parameters.AddWithValue("@indescripcion", indescripcion);
                cmd.Parameters.AddWithValue("@inTIPO", tipo);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                adpnota3 = new MySqlDataAdapter(cmd);
                adpnota3.Fill(data);

                return data;


            }
            catch (Exception ex)
            {
                throw ex;

            }

            finally
            {
                conexion.Close();
                cmd.Dispose();

            }
        }
        */

        public DataTable DATA_OBS_Listar(String inSemestre, String inCodAlumno, String inNombre, String indescripcion, String tipo)
        {
           
             using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_DATA_LISTA_DATA_OBS";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@inSemestre", inSemestre);
                    SqlCmd.Parameters.AddWithValue("@inCodAlumno", inCodAlumno);
                    SqlCmd.Parameters.AddWithValue("@inNombre", inNombre);
                    SqlCmd.Parameters.AddWithValue("@indescripcion", indescripcion);
                    SqlCmd.Parameters.AddWithValue("@inTIPO", tipo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


        public DataTable DATA_MATRIC1_ListaNominas_PorSemestre(String semestre, String ciclo, String especialidad)
        {
            //Lista todas las especialidades de la tabla data_notas

            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter adpnota3 = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();

            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_DATA_MATRIC1_ListaNominas_PorSemestre";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_ciclo", ciclo);
                cmd.Parameters.AddWithValue("@in_Especialidad", especialidad);


                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                adpnota3 = new MySqlDataAdapter(cmd);
                adpnota3.Fill(data);

                return data;


            }
            catch (Exception ex)
            {
                throw ex;

            }

            finally
            {
                conexion.Close();
                cmd.Dispose();

            }
        }

        public void DATA_MATRIC1_ActualizaFlag_Confirmado(String semestre, String ciclo, String especialidad)
        {

            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conexion = new MySqlConnection();

            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;

            try
            {
                conexion.Open();

                cmd.CommandText = "sp_DATA_MATRIC1_ActualizaFlag_Confirmado";
                cmd.Connection = conexion;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_ciclo", ciclo);
                cmd.Parameters.AddWithValue("@in_cod_espec", especialidad);



                cmd.CommandTimeout = 3600;
                cmd.ExecuteNonQuery();



            }

            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexion.Close();
                cmd.Dispose();
            }
        }



        public void Data_obs_Insert(
          string inSEMESTRE,
          string inCOD_ALUMNO,
          string inCONDICION,
          string inESTADO,
          string inDESCRIP,
          string inOBSERVAC,
          string inCICLO,
          string inACTAOERA)
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();


            MySqlConnection conexion = new MySqlConnection();
            MySqlTransaction tran = null;
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;

            try
            {

                conexion.Open();
                tran = conexion.BeginTransaction();
                cmd.CommandText = "sp_DATA_OBS_Insert";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                cmd.Parameters.AddWithValue("@inSEMESTRE", inSEMESTRE);
                cmd.Parameters.AddWithValue("@inCOD_ALUMNO", inCOD_ALUMNO);
                cmd.Parameters.AddWithValue("@inCONDICION", inCONDICION);
                cmd.Parameters.AddWithValue("@inESTADO", inESTADO);
                cmd.Parameters.AddWithValue("@inDESCRIP", inDESCRIP);
                //string MySQLFormatDate1 = infechaingreso.ToString("inDESCRIP/MM/dd HH:mm:ss");
                //string MySQLFormatDate2 = infechafin.ToString("yyyy/MM/dd HH:mm:ss");
                cmd.Parameters.AddWithValue("@inOBSERVAC", inOBSERVAC);
                cmd.Parameters.AddWithValue("@inCICLO", inCICLO);
                cmd.Parameters.AddWithValue("@inACTAOERA", inACTAOERA);
                cmd.CommandTimeout = 3600;
                cmd.ExecuteNonQuery();

                tran.Commit();


            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;

            }

            finally
            {
                conexion.Close();
                cmd.Dispose();
            }

        }


        public DataTable DATA_Maestro_buscar(String inCodAlumno, String inNombre)
        {
            //Lista todas las Licenciass

            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter adpnota3 = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();

            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_DATA_Maestro_buscar";
                cmd.Parameters.AddWithValue("@inCodAlumno", inCodAlumno);
                cmd.Parameters.AddWithValue("@inNombre", inNombre);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                adpnota3 = new MySqlDataAdapter(cmd);
                adpnota3.Fill(data);

                return data;


            }
            catch (Exception ex)
            {
                throw ex;

            }

            finally
            {
                conexion.Close();
                cmd.Dispose();

            }
        }




        public DataTable Lista_Nomina(String inSemestre, String inGrupo, String inCiclo)
        {

            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_Formato_Nomina";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", inSemestre);
                    SqlCmd.Parameters.AddWithValue("@in_grupo", inGrupo);
                    SqlCmd.Parameters.AddWithValue("@in_ciclos", inCiclo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }



    }
}
    




    

